package com.spring.service.content;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import com.spring.domain.Content;
import com.spring.domain.File;
import com.spring.domain.RelContImg;
import com.spring.domain.User;
import com.spring.domain.vo.ImgContent;
import com.spring.service.util.ContentMapper;
import com.spring.service.util.FileMapper;
import com.spring.service.util.RelContImgMapper;
import com.spring.service.util.UserMapper;

public class JdbcContentDao extends SimpleJdbcDaoSupport implements ContentDao {
	protected final Log logger = LogFactory.getLog(getClass());

	public void deleteContent(List<Content> contents) {
		String idList = "";
		for (Iterator<Content> iterator = contents.iterator(); iterator
				.hasNext();) {
			Content user = (Content) iterator.next();
			idList += user.getId();
			if (iterator.hasNext())
				idList += ",";
		}

		int count = getSimpleJdbcTemplate().update(
				"delete from content where id in (" + idList + ")");
		
		logger.info("Deleted  " + count + " contents");
		
		count = getSimpleJdbcTemplate().update(
				"delete from rel_cont_file where id_cont in (" + idList + ")");
		logger.info("Deleted  " + count + " relation with file");
		
		count = getSimpleJdbcTemplate().update(
				"delete from rel_cont_gallery where id_cont in (" + idList + ")");
		logger.info("Deleted  " + count + " relation with gallery");
		
		count = getSimpleJdbcTemplate().update(
				"delete from rel_cont_img where id_cont in (" + idList + ")");
		logger.info("Deleted  " + count + " relation with image");
		
		count = getSimpleJdbcTemplate().update(
				"delete from rel_cont_user where idCont in (" + idList + ")");
		logger.info("Deleted  " + count + " relation with user");
		
	}

	public List<Content> getContents() {
		return getSimpleJdbcTemplate()
				.query("select id,titolo,strillo,descrizione,startDate,endDate,fk_tipo_cont,fk_tipo_skin,insDate,hasPrivate,hasGallery,hasFile,hasImg from content",
						new ContentMapper());
	}

	public int insertContent(Content content) {
		logger.info("Saving content id[:" + content.getId() + "]");
		int count = getSimpleJdbcTemplate()
				.update("insert content (titolo,strillo,descrizione,startDate,endDate,fk_tipo_cont,fk_tipo_skin,insDate,hasPrivate,hasGallery,hasFile,hasImg) "
						+ "values (:titolo,:strillo,:descrizione,:startDate,:endDate,:fk_tipo_cont, :fk_tipo_skin ,:insDate,:hasPrivate,:hasGallery,:hasFile,:hasImg )",
						ContentMapper.sqlMap(content));

		logger.info("Rows affected: " + count);

		return getJdbcTemplate().queryForInt("select last_insert_id()");
	}
	
	public List<Content> getContentsByType(int id) {
		return getSimpleJdbcTemplate()
				.query("select id,titolo,strillo,descrizione,startDate,endDate,fk_tipo_cont,fk_tipo_skin,insDate,hasPrivate,hasGallery,hasFile,hasImg from content where fk_tipo_cont="+id,
						new ContentMapper());
	}
	
	public Content selectById(int id) {
		return (getSimpleJdbcTemplate()
				.query("select id ,titolo ,strillo,descrizione,startDate,endDate,fk_tipo_cont,fk_tipo_skin,insDate,hasPrivate,hasGallery,hasFile,hasImg from content where id="
						+ id, new ContentMapper())).get(0);
	}

	public List<Content> selectByMail(Content content) {
		return getSimpleJdbcTemplate()
				.query("select id ,titolo ,strillo,descrizione,startDate,endDate,fk_tipo_cont,fk_tipo_skin,insDate,hasPrivate,hasGallery,hasFile,hasImg from content where mail='"
						+ content.getId() + "'", new ContentMapper());
	}

	public void updateContent(Content content) {
		logger.info("Update Content id[: " + content.getId() + "]");
		int count = getSimpleJdbcTemplate()
				.update("update content  set "
						+ "titolo=:titolo ,strillo=:strillo,descrizione=:descrizione,"
						+ "startDate=:startDate,endDate=:endDate,fk_tipo_cont=:fk_tipo_cont,fk_tipo_skin=:fk_tipo_skin,"
						+ "insDate=:insDate,hasPrivate=:hasPrivate,hasGallery=:hasGallery,hasFile=:hasFile,hasImg=:hasImg where id=:id",
						ContentMapper.sqlMap(content));
		logger.info("Rows affected: " + count);
	}
	
	public List<Content> getGalleriesRelated(int contentId) {
		return getSimpleJdbcTemplate()
				.query("select id ,titolo ,strillo,descrizione,startDate,endDate,fk_tipo_cont,fk_tipo_skin,insDate,hasPrivate,hasGallery,hasFile,hasImg from content as content "
						+ "where content.id in (select id_cont_gallery from rel_cont_gallery where id_cont="
						+ contentId + ")", new ContentMapper());
	}

	public List<User> getUsersRelated(int contentId) {
		return getSimpleJdbcTemplate()
				.query("select id, mail, pwd, startDate, activationCode, newsLetter, admin from user as user "
						+ "where user.id in (select idUser from rel_cont_user where idCont="
						+ contentId + ")", new UserMapper());
	}

	// User relations
	public void insertUserRelation(List<User> users, int idContent) {
		logger.info("Saving rel_cont_user idUsers:[" + users.toString()
				+ "], idCont:[" + idContent + "]");

		for (Iterator<User> iter = users.iterator(); iter.hasNext();) {
			logger.info("Inserting relations.. ");
			User user = (User) iter.next();
			getSimpleJdbcTemplate().update(
					"insert rel_cont_user (idUser,idCont) values ("
							+ user.getId() + ", " + idContent + ")");
			logger.info("Relation Inserted.. ");
		}
	}

	public void deleteUserRelation(List<User> users, int idContent) {
		logger.info("deleting rel_cont_user idUsers:[" + users.toString()
				+ "], idCont:[" + idContent + "]");

		String idUsers = "";
		for (Iterator<User> iterator = users.iterator(); iterator.hasNext();) {
			User user = (User) iterator.next();
			idUsers += user.getId();
			if (iterator.hasNext())
				idUsers += ",";
		}

		int count = getSimpleJdbcTemplate().update(
				"delete from rel_cont_user where idUser in (" + idUsers
						+ ") and idCont=" + idContent);
		logger.info("Rows affected: " + count);
	}
	
	public void	insertGalleryRelation(List<Content> galleries,int idContent){
		logger.info("Saving rel_cont_gallery galleries:[" + galleries.toString()
				+ "], idCont:[" + idContent + "]");

		for (Iterator<Content> iter = galleries.iterator(); iter.hasNext();) {
			logger.info("Inserting relations.. ");
			Content contentGallery = (Content) iter.next();
			getSimpleJdbcTemplate().update(
					"insert rel_cont_gallery (id_cont_gallery,id_cont) values ("
							+ contentGallery.getId() + ", " + idContent + ")");
			logger.info("Relation Inserted.. ");
		}
	}
	public void	 deleteGalleryRelation(List<Content> galleries,int idContent){
		logger.info("deleting rel_cont_gallery galleries:[" + galleries.toString()
				+ "], idCont:[" + idContent + "]");

		String idGalleries = "";
		for (Iterator<Content> iterator = galleries.iterator(); iterator.hasNext();) {
			Content contentGallery = (Content) iterator.next();
			idGalleries += contentGallery.getId();
			if (iterator.hasNext())
				idGalleries += ",";
		}

		int count = getSimpleJdbcTemplate().update(
				"delete from rel_cont_gallery where id_cont_gallery in (" + idGalleries
						+ ") and id_cont=" + idContent);
		logger.info("Rows affected: " + count);
	}
	
	

	public void deleteImageRelation(List<File> files, int idContent) {
		logger.info("deleting rel_cont_img id_file:[" + files.toString()
				+ "], id_cont:[" + idContent + "]");

		String id = "";
		for (Iterator<File> iterator = files.iterator(); iterator.hasNext();) {
			File file = (File) iterator.next();
			id += file.getId();
			if (iterator.hasNext())
				id += ",";
		}

		int count = getSimpleJdbcTemplate().update(
				"delete from rel_cont_img where id_file in (" + id
						+ ") and id_cont=" + idContent);
		logger.info("Rows affected: " + count);
	}

	public void insertImgRelation(List<File> files, int idContent) {
		logger.info("Saving rel_cont_img idFiles:[" + files.toString()
				+ "], idCont:[" + idContent + "]");

		for (Iterator<File> iter = files.iterator(); iter.hasNext();) {
			logger.info("Inserting relations.. ");
			File file = (File) iter.next();
			getSimpleJdbcTemplate().update(
					"insert rel_cont_img (id_file,id_cont) values ("
							+ file.getId() + ", " + idContent + ")");
			logger.info("Relation Inserted");
		}
	}

	public void updateImgRelationPosition(File file, int idContent, int position) {
		logger.info("Saving position rel_cont_img file:[" + file.toString()
				+ "], idCont:[" + idContent + "], position:[" + position + "]");

		logger.info("Updating position for image relations.. ");
		getSimpleJdbcTemplate().update(
				"update rel_cont_img set position=" + position
						+ " where id_file=" + file.getId() + " and id_cont="
						+ idContent);
		logger.info("Position updated for image");
	}

	public List<ImgContent> getImgRelated(int contentId) {

		List<RelContImg> relContImg = getSimpleJdbcTemplate().query(
				"select id, id_cont, id_file, position from rel_cont_img as relContImg "
						+ "where relContImg.id_cont=" + contentId,
				new RelContImgMapper());
		List<ImgContent> objReturn = new ArrayList<ImgContent>();

		if (!CollectionUtils.isEmpty(relContImg)) {
			String idList = "";
			for (Iterator<RelContImg> iterator = relContImg.iterator(); iterator
					.hasNext();) {
				RelContImg obj = (RelContImg) iterator.next();
				idList += obj.getId_file();
				if (iterator.hasNext())
					idList += ",";
			}

			List<File> relatedFiles = getSimpleJdbcTemplate().query(
					"select id, name, description, type from file as file "
							+ "where file.id in (" + idList + ")",
					new FileMapper());

			for (Iterator<File> iterator = relatedFiles.iterator(); iterator
					.hasNext();) {
				File file = (File) iterator.next();

				ImgContent obj = new ImgContent();
				obj.setFile(file);

				for (Iterator<RelContImg> iterator2 = relContImg.iterator(); iterator2
						.hasNext();) {
					RelContImg obj2 = (RelContImg) iterator2.next();
					if (obj2.getId_file() == file.getId()) {
						obj.setRelContImg(obj2);
						objReturn.add(obj);
					}
				}

			}
		}
		return objReturn;
	}

	public List<ImgContent> getImg() {

		List<File> imageFiles = getSimpleJdbcTemplate().query(
				"select id, name, description, type from file as file "
						+ "where file.type like '%image%'", new FileMapper());
		List<ImgContent> objReturn = new ArrayList<ImgContent>();

		for (Iterator<File> iterator = imageFiles.iterator(); iterator
				.hasNext();) {
			File file = (File) iterator.next();
			ImgContent obj = new ImgContent();
			obj.setFile(file);
			objReturn.add(obj);
		}

		return objReturn;
	}


	// MANAGE FILE RELATION
	public List<File> getFileRelated(int contentId) {
		List<File> relatedFiles = getSimpleJdbcTemplate().query(
					"select file.id, file.name, file.description, file.type from file as file "
							+ "where file.id in (select id_file from rel_cont_file where id_cont="+contentId+")",
					new FileMapper());
		return relatedFiles;
		
	}
	public List<File> getFile() {
		List<File> files = getSimpleJdbcTemplate().query(
				"select id, name, description, type from file as file "
						+ "where file.type not like '%image%'", new FileMapper());
		return files;
	}
	public void insertDocumentRelation(List<File> files, int idContent) {
		logger.info("Saving rel_cont_file idFiles:[" + files.toString()+ "], idCont:[" + idContent + "]");

		for (Iterator<File> iter = files.iterator(); iter.hasNext();) {
			logger.info("Inserting relations.. ");
			File file = (File) iter.next();
			getSimpleJdbcTemplate().update(
					"insert rel_cont_file (id_file,id_cont) values ("
							+ file.getId() + ", " + idContent + ")");
			logger.info("Relation Inserted");
		}
	}

	public void deleteDocumentRelation(List<File> files, int idContent) {
		logger.info("deleting rel_cont_file id_file:[" + files.toString()+ "], id_cont:[" + idContent + "]");

		String id = "";
		for (Iterator<File> iterator = files.iterator(); iterator.hasNext();) {
			File file = (File) iterator.next();
			id += file.getId();
			if (iterator.hasNext())
				id += ",";
		}

		int count = getSimpleJdbcTemplate().update(
				"delete from rel_cont_file where id_file in (" + id
						+ ") and id_cont=" + idContent);
		logger.info("Rows affected: " + count);
	}
}
